/**
**
** Ejercicio 1
**
**/

DECLARE @temp1 table (Id int identity (1,1),Nombre varchar(100), Cantidad nvarchar(MAX))

DECLARE @qry nvarchar(MAX)
DECLARE @table nvarchar(MAX)
DECLARE @column nvarchar(MAX)
DECLARE @qty nvarchar(MAX)

DECLARE Table_Crawler CURSOR FOR 
select st.Name, sc.name
	from sys.tables st join sys.columns sc 
		on st.object_id= sc.object_id
	where sc.column_id=1

OPEN Table_Crawler

FETCH NEXT FROM Table_Crawler into @table, @column

WHILE @@FETCH_STATUS=0
	BEGIN
		SET @qry = 'SELECT @count=ISNULL(MAX('+@column+'),0) FROM '+quotename(@table)
		EXEC sp_executesql @qry, N'@count nvarchar(max) output', @count=@qty OUTPUT
		INSERT INTO @temp1 (Nombre,Cantidad) VALUES (@table,@qty)
		FETCH NEXT FROM Table_Crawler into @table, @column
	END

CLOSE Table_Crawler

DEALLOCATE Table_Crawler 

/**
**
** Ejercicio 2
**
**/

SELECT * from @temp1

DECLARE @temp1 table (Id int identity (1,1),Nombre varchar(100), Cantidad nvarchar(MAX))

DECLARE @qry nvarchar(MAX)
DECLARE @table nvarchar(MAX)
DECLARE @column nvarchar(MAX)
DECLARE @schema nvarchar(MAX)
DECLARE @qty nvarchar(MAX)

DECLARE Table_Crawler CURSOR FOR 
select ss.name,st.Name
	from sys.tables st
		join sys.schemas ss
		on ss.schema_id=st.schema_id

OPEN Table_Crawler

FETCH NEXT FROM Table_Crawler into @schema, @table

WHILE @@FETCH_STATUS=0
	BEGIN
		SET @qry = 'SELECT @count=count(*) FROM '+quotename(@schema)+'.'+quotename(@table)
		EXEC sp_executesql @qry, N'@count nvarchar(max) output', @count=@qty OUTPUT
		INSERT INTO @temp1 (Nombre,Cantidad) VALUES (@schema+'.'+@table,@qty)
		FETCH NEXT FROM Table_Crawler into @schema, @table
	END

CLOSE Table_Crawler

DEALLOCATE Table_Crawler 

SELECT * from @temp1